var express = require('express');
var Msdb = require("../../../db/Mysqldb.js");
const multer = require('multer');
const path = require('path');
var router = express.Router();
const fs = require('fs'); // 引入fs模块检查目录
const crypto = require('crypto');

// 确保sales目录存在
const salesDir = path.join(__dirname, '../../../../sales/');
if (!fs.existsSync(salesDir)) {
    fs.mkdirSync(salesDir, { recursive: true })
}

// 配置multer存储
const storage = multer.diskStorage({
    destination: (_req, _file, cb) => {
        cb(null, salesDir);
    },
    filename: (req, file, cb) => {
        const timeStp = Date.now();
        const ext = path.extname(file.originalname);
        // 生成32位随机字符串
        const randomStr = crypto.randomBytes(16).toString('hex');
        const filename = `${req.auth.user_id||req.auth.user_id}_${timeStp}_${randomStr}${ext}`
        cb(null, filename);
    }
});

const upload = multer({ storage });

var db = new Msdb();
var getTime = () => {
    return new Date(new Date().getTime() + 8 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');
}

router.post("/uploadorder", upload.fields([
    { name: 'business_contract', maxCount: 1 },
    { name: 'technical_agreement', maxCount: 1 }
]), async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    let result = {};
    const uploadedFiles = []; // 记录已上传文件路径
    
    try {
        const data = { ...req.body };
        const { money } = data; // 提取金额字段，非必填
        
        // 解析产品列表（前端传递的JSON字符串）
        let productList = [];
        if (data.product_list) {
            try {
                productList = JSON.parse(data.product_list);
                console.log("收到的产品列表：", productList);
            } catch (e) {
                throw new Error("产品列表格式错误");
            }
        }
        
        // 2. 检查订单号是否重复
        const [orders] = await connection.execute(
            `SELECT id FROM sales_order WHERE order_id=?`, 
            [data.order_id]
        );
        
        if (orders.length !== 0) {
            // 删除已保存的附件
            if (req.files) {
                Object.keys(req.files).forEach(fileField => {
                    req.files[fileField].forEach(file => {
                        const filePath = path.join(salesDir, file.filename);
                        if (fs.existsSync(filePath)) {
                            fs.unlinkSync(filePath);
                            uploadedFiles.push(filePath);
                            console.log(`删除重复订单的文件: ${filePath}`);
                        }
                    });
                });
            }
            
            result = {
                code: 201,
                msg: "订单号重复"
            };
            res.json(result);
            return;
        }

        // 3. 处理商务合同引用单号
        if (data.business_contract_reference) {
            const [refBusinessorders] = await connection.execute(
                `SELECT business_contract FROM sales_order WHERE order_id=?`, 
                [data.business_contract_reference]
            );
            
            if (refBusinessorders.length === 0) {
                throw new Error(`商务合同引用的单号【${data.business_contract_reference}】不存在`);
            }
            
            const refBusinessContract = refBusinessorders[0].business_contract;
            if (!refBusinessContract) {
                throw new Error(`商务合同引用的单号【${data.business_contract_reference}】没有对应的文件`);
            }
            
            data.business_contract = refBusinessContract;
        } 
        else if (req.files['business_contract']) {
            const file = req.files['business_contract'][0];
            data.business_contract = file.filename;
        }

        // 4. 处理技术协议引用单号
        if (data.technical_agreement_reference) {
            const [refTechnicalorders] = await connection.execute(
                `SELECT technical_agreement FROM sales_order WHERE order_id=?`, 
                [data.technical_agreement_reference]
            );
            
            if (refTechnicalorders.length === 0) {
                throw new Error(`技术协议引用的单号【${data.technical_agreement_reference}】不存在`);
            }
            
            const refTechnicalAgreement = refTechnicalorders[0].technical_agreement;
            if (!refTechnicalAgreement) {
                throw new Error(`技术协议引用的单号【${data.technical_agreement_reference}】没有对应的文件`);
            }
            
            data.technical_agreement = refTechnicalAgreement;
        } 
        else if (req.files['technical_agreement']) {
            const file = req.files['technical_agreement'][0];
            data.technical_agreement = file.filename;
        }

        // 5. 统一处理空值（新增money字段，允许为null）
        const formattedData = {
            ...data,
            order_id: data.order_id || null,
            contract_number: data.contract_number || null, 
            customer_id: data.customer_id || null,
            money: money ? parseFloat(money) : null, 
            order_date: data.order_date || null,
            quality_guarantee_date: data.quality_guarantee_date || null, 
            remark: data.remark || null,
            business_contract: data.business_contract || null,
            technical_agreement: data.technical_agreement || null,
            business_contract_reference: data.business_contract_reference || null,
            technical_agreement_reference: data.technical_agreement_reference || null,
            insert_user_id: req.auth.user_id,
            create_time: getTime()
        };
        
        // 验证必要参数
        if (!formattedData.order_id) {
            throw new Error("订单号不能为空");
        }
        if (!formattedData.contract_number) { 
            throw new Error("合同编号不能为空");
        }
        if (!formattedData.customer_id) {
            throw new Error("客户信息不能为空");
        }
        if (productList.length === 0) {
            throw new Error("至少需要添加一个设备信息");
        }
        productList.forEach((product, index) => {
            if (!product.product_part_number) {
                throw new Error(`第${index + 1}个设备的产品料号不能为空`);
            }
            if (!product.specification_and_model) {
                throw new Error(`第${index + 1}个设备的规格型号不能为空`);
            }
            if (!product.quantity || product.quantity < 1) {
                throw new Error(`第${index + 1}个设备的数量必须大于0`);
            }
        });
        
        // 开启事务
        await db.beginTransaction(connection);
        
        // 新增销售订单（添加money字段）
        await connection.execute(
            `INSERT INTO sales_order (
                order_id, contract_number, customer_id, money,
                order_date, remark, business_contract, technical_agreement, 
                insert_user_id, create_time
             ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                formattedData.order_id, 
                formattedData.contract_number,
                formattedData.customer_id,
                formattedData.money, 
                formattedData.order_date, 
                formattedData.remark, 
                formattedData.business_contract,
                formattedData.technical_agreement,
                formattedData.insert_user_id, 
                formattedData.create_time
            ]
        );
        
        // 批量插入产品信息到product表（保持不变）
        const currentTime = getTime();
        const insertUserId = formattedData.insert_user_id;
        const salesorderId = formattedData.order_id;
        
        const productInsertSql = `
            INSERT INTO product (
                insert_user_id, create_time, sales_order_id, 
                product_part_number, specification_and_model,
                quality_guarantee_date  
            ) VALUES (?, ?, ?, ?, ?, ?)  
        `;
        
        const productValues = [];
        productList.forEach(product => {
            for (let i = 0; i < product.quantity; i++) {
                productValues.push([
                    insertUserId,
                    currentTime,
                    salesorderId,
                    product.product_part_number,
                    product.specification_and_model,
                    formattedData.quality_guarantee_date
                ]);
            }
        });
        
        if (productValues.length > 0) {
            for (const values of productValues) {
                await connection.execute(productInsertSql, values);
            }
            console.log(`成功插入${productValues.length}条产品记录`);
        }
        
        // 生成日志信息（添加金额字段）
        const productSummary = productList.map(item => 
            `[${item.product_part_number}-${item.specification_and_model}-${item.quantity}台]`
        ).join('; ');
        
        const logMessage = [
            `订单号: ${formattedData.order_id}`,
            `合同编号: ${formattedData.contract_number}`,
            `客户ID: ${formattedData.customer_id}`,
            formattedData.money ? `订单金额: ¥${formattedData.money}` : '', // 金额存在时才显示
            formattedData.order_date ? `订单日期: ${formattedData.order_date}` : '',
            formattedData.quality_guarantee_date ? `质保日期: ${formattedData.quality_guarantee_date}` : '',
            formattedData.remark ? `备注: ${formattedData.remark}` : '',
            `产品列表: ${productSummary}`,
            formattedData.business_contract ? `商务合同: 已上传` : '',
            formattedData.technical_agreement ? `技术协议: 已上传` : ''
        ].filter(Boolean).join('; ');
        
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?);`,
            [formattedData.insert_user_id, `用户ID为${formattedData.insert_user_id}新增销售订单：${logMessage}`, formattedData.create_time]
        );
        
        // 提交事务
        await db.commit(connection);
        
        result = {
            code: 200,
            msg: "上传成功",
            data: {
                orderId: formattedData.order_id,
                contractNumber: formattedData.contract_number,
                money: formattedData.money, // 返回金额信息
                productCount: productValues.length
            }
        };
    } catch (error) {
        // 发生错误时回滚事务
        await db.rollback(connection);
        
        // 回滚时删除已上传的文件
        if (req.files) {
            Object.keys(req.files).forEach(fileField => {
                req.files[fileField].forEach(file => {
                    const filePath = path.join(salesDir, file.filename);
                    if (fs.existsSync(filePath)) {
                        fs.unlinkSync(filePath);
                        console.log(`事务回滚，删除文件: ${filePath}`);
                    }
                });
            });
        }
        
        result = {
            code: 400,
            msg: error.message || "服务器内部错误"
        };
    } finally {
        // 确保连接释放
        connection.release();
    }
    
    res.json(result);
});
    
router.post("/updateorder", upload.fields([
    { name: 'business_contract', maxCount: 1 },
    { name: 'technical_agreement', maxCount: 1 }
]), async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    let result = {}
    
    try {
        // 1. 验证更新用户ID
        const data = { ...req.body };
        if (!req.auth.user_id) {
            result = { code: 400, msg: "没有更新用户ID，不允许更新" };
            return res.json(result);
        }
        
        // 2. 处理上传文件
        const fileFields = ['business_contract', 'technical_agreement'];
        fileFields.forEach(field => {
            if (req.files?.[field]?.[0]) {
                data[field] = req.files[field][0].filename;
            }
        });
        
        // 3. 设置字段默认值
        const fieldsWithDefaultNull = [
            'order_date','money', 'remark', ...fileFields
        ];
        fieldsWithDefaultNull.forEach(field => {
            data[field] = data[field] || null;
        });
        
        // 4. 查询原始数据用于比较
        const [originalRows] = await connection.execute(
            'SELECT * FROM sales_order WHERE id = ?',
            [data.id]
        );
        
        if (originalRows.length === 0) {
            result = { code: 404, msg: "销售订单记录不存在" };
            return res.json(result);
        }
        const originalData = originalRows[0];
        
        // 5. 比较字段变化（移除交货日期，优化客户信息不被修改）
        const fieldsToCheck = [
            'order_date', 'money', 'remark', ...fileFields
        ];
        
        // 辅助函数：处理数值型字段的比较（统一转为数字后比较）
        const isNumberFieldChanged = (oldValue, newValue) => {
            // 空值统一处理（null/undefined/空字符串视为相同）
            if ([null, undefined, ''].includes(oldValue) && [null, undefined, ''].includes(newValue)) {
                return false;
            }
            // 转为数字后比较（避免字符串与数字的类型差异）
            const numOld = oldValue === null ? null : Number(oldValue);
            const numNew = newValue === null ? null : Number(newValue);
            return numOld !== numNew;
        };
        
        const changes = [];
        const updateFields = [];
        const updateValues = [];
        
        // 检查每个字段是否有变化
        fieldsToCheck.forEach(field => {
            const newValue = data[field];
            const oldValue = originalData[field];
            
            let isDifferent;
            // 对金额字段单独处理
            if (['money'].includes(field)) {
                isDifferent = isNumberFieldChanged(oldValue, newValue);
            } else {
                // 其他字段保持原有逻辑
                isDifferent = JSON.stringify(newValue) !== JSON.stringify(oldValue);
            }
            
            if (isDifferent) {
                changes.push({
                    field,
                    oldValue: oldValue ?? '空',
                    newValue: newValue ?? '空'
                });
                updateFields.push(`${field} = ?`);
                updateValues.push(newValue);
            }
        });
        
        // 6. 没有变化则不执行更新
        if (changes.length === 0) {
            result = { code: 200, msg: "没有需要更新的内容" };
            return res.json(result);
        }
        
        // 7. 执行更新
        updateFields.push('update_time = ?', 'update_user_id = ?');
        updateValues.push(getTime(), req.auth.user_id, data.id);
        
        // 开启事务
        await db.beginTransaction(connection);
        
        // 执行更新
        await connection.execute(
            `UPDATE sales_order SET ${updateFields.join(', ')} WHERE id = ?`,
            updateValues
        );
        
        // 8. 记录操作日志（优化日志信息）
        const changeDescription = changes.map(change => 
            `${change.field}: ${change.oldValue} → ${change.newValue}`
        ).join('; ');
        
        // 补充订单号到日志，便于追踪
        const logMessage = `用户ID: ${req.auth.user_id} 更新了销售订单[ID: ${data.id}, 订单号: ${originalData.order_id}]，变更内容: ${changeDescription}`;
        
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
            [req.auth.user_id, logMessage, getTime()]
        );
        
        // 提交事务
        await db.commit(connection);
        
        result = {
            code: 200,
            msg: "更新成功",
            changes: changes
        };
    } catch (error) {
        // 发生错误时回滚事务
        await db.rollback(connection);
        
        // 回滚时删除新上传的文件
        if (req.files) {
            Object.keys(req.files).forEach(fileField => {
                req.files[fileField].forEach(file => {
                    const filePath = path.join(salesDir, file.filename);
                    if (fs.existsSync(filePath)) {
                        fs.unlinkSync(filePath);
                        console.log(`事务回滚，删除文件: ${filePath}`);
                    }
                });
            });
        }
        
        result = {
            code: 500,
            msg: "服务器内部错误: " + error.message // 增加错误详情便于调试
        };
    } finally {
        // 确保连接释放
        connection.release();
    }
    
    res.json(result);
});

router.post('/searchSales', async function(req, res) {
    var result = {};
    try {
        var data = req.body;
        // 获取控制参数：是否需要关联查询客户信息（默认false）
        var includeCustomer = data.includeCustomer || false;
        
        // 基础查询语句（销售订单表）
        var sql = "SELECT sales_order.*";
        var getTotalSql = "SELECT COUNT(*) AS total FROM sales_order WHERE 1=1";
        var params = [];
        
        // 如果需要关联客户信息，添加JOIN语句
        if (includeCustomer) {
            sql += ", customer.name AS customerName,customer.phone AS customerPhone FROM sales_order LEFT JOIN customer ON sales_order.customer_id = customer.id";
        } else {
            sql += " FROM sales_order";
        }
        
        // 基础查询条件
        sql += " WHERE 1=1";
        
        // 遍历查询条件
        for (const key in data.condition) {
            if (data.condition[key] != null) {
                // 订单号和合同编号使用模糊查询，其他字段保持精确匹配
                if (key === 'order_id' || key === 'contract_number') {
                    // 模糊查询使用LIKE，参数需要添加通配符
                    sql += ` AND sales_order.${key} LIKE ?`;
                    getTotalSql += ` AND ${key} LIKE ?`;
                    params.push(`%${data.condition[key]}%`); // 添加前后通配符
                } else {
                    // 其他字段保持精确匹配
                    sql += ` AND sales_order.${key} = ?`;
                    getTotalSql += ` AND ${key} = ?`;
                    params.push(data.condition[key]);
                }
            }
        }
        
        // 获取总条数
        var [totalResult] = await db.query(getTotalSql, params);
        var total = totalResult.total;
        
        // 分页处理
        sql += " LIMIT ? OFFSET ?";
        // 复制查询参数并添加分页参数
        var queryParams = [...params, data.pageSize, data.offset];

        // 执行查询
        var salesList = await db.query(sql, queryParams);

        for(var i=0;i<salesList.length;i++){//查询每个销售订单对应的设备总数
            var [total_number]=await db.query("SELECT COUNT(*) AS total FROM product WHERE sales_order_id=?",[salesList[i].order_id])
            salesList[i].total_number=total_number.total
        }
        
        // 处理返回结果（如果有客户信息，可单独封装）
        if (includeCustomer) {
            salesList = salesList.map(item => {
                // 分离销售订单信息和客户信息，避免字段冲突
                const orderInfo = {};
                const customerInfo = {};
                for (const key in item) {
                    // 假设customer表的字段不会和sales_order表重名，或根据实际表结构调整
                    if (['customerId', 'customerName', 'customerPhone'].includes(key)) {
                        customerInfo[key] = item[key];
                    } else {
                        orderInfo[key] = item[key];
                    }
                }
                return {
                    ...orderInfo,
                    customer: customerInfo
                };
            });
        }
        
        // 构建返回结果
        result = {
            data: salesList,
            code: 200,
            msg: "查询成功~",
            total: total
        };
    } catch (error) {
        console.error('查询销售订单失败:', error);
        result = {
            code: 500,
            msg: '查询失败: ' + error.message,
            data: []
        };
    }
    res.send(JSON.stringify(result));
});

// 错误处理中间件
router.use((err, _req, res, next) => {
    if (err instanceof multer.MulterError) {
        console.error("Multer文件上传错误:", err);
        return res.status(500).json({ 
            code: 500, 
            msg: `文件上传错误: ${err.message}`
        });
    }
    next(err);
});

module.exports = router;